Open Data Barcelona

Pla Clima

El canvi climàtic és una realitat i està ocasionat per l’ésser humà. Ja tenim evidències dels seus impactes i cal actuar per a fer-hi front.

Les ciutats són especialment vulnerables, ja que concentren la majoria de la població mundial i és on l’energia es consumeix de manera més intensiva, generant el 70% de les emissions de gasos amb efecte d’hivernacle.

Barcelona és una ciutat mediterrània, que consumeix poca energia i genera poques emissions per càpita en relació a altres ciutats similars, però encara té molt camí per recórrer, ja que té una elevada dependència de recursos fòssils i nuclears.

Els efectes del canvi climàtic podrien presentar riscos en termes de salut i benestar de les persones (onades de calor), de seguretat (garantia de subministrament d’aigua i d’energia, vulnerabilitat de les infraestructures, risc d’incendis..) i en l’entorn natural que cal preveure i prevenir a nivell global.

Amb motiu de la celebració a París de la COP21, la 21a Conferència de les Parts de la Convenció Marc de les Nacions Unides sobre el Canvi Climàtic, i en el marc del Compromís Ciutadà per la Sostenibilitat, Barcelona va concretar un Compromís de Barcelona pel Clima, en què es comprometia a reduir les emissions de gasos em efecte hivernacle un 40% al 2030 en relació al 2005 i augmentar 1,6km2 de verd urbà com a mesura d’adaptació.

Ajuntament i ciutadania van establir un Full de Ruta 2015-2017 amb projectes municipals i ciutadans per aconseguir aquests objectius. A partir de l’experiència d’aquests dos anys l’Ajuntament vol donar una resposta més potent i estructurada a aquest compromís i per això es proposa aglutinar les accions que du a terme al voltant del repte del canvi climàtic en un únic pla que integri totes les línies de treball: el Pla Clima.

És un pla que alhora concreta els compromisos internacionals signats per l’Ajuntament, com és el Pacte d’Alcaldes i Alcaldesses pel Clima i l’Energia Sostenible.

Qualitat de l’aire de la ciutat de Barcelona

Es mostren dades dels contaminants mesurats a les estacions de la ciutat de Barcelona. L’actualització es realitza en intervals d’una hora indicant si el valor està o no validat i també es mostren les dades dels tres dies anteriors a l’actual. Tanmateix es publiquen històrics amb periodicitat mensual.

library(data.table) 
library(dplyr)
library(lubridate)

Attaching package: ‘lubridate’

The following objects are masked from ‘package:data.table’:

    hour, isoweek, mday, minute, month, quarter, second, wday, week, yday, year

The following object is masked from ‘package:base’:

    date

gener <- tbl_df(fread("data/2019_01_Gener_qualitat_aire_BCN.csv",
               header=TRUE))
gener %>% select(nom_cabina, longitud, latitud) %>% distinct()

gener2 <- gener %>%
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         generat = dmy_hm(generat)
         ) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, generat)
summary(gener2)

          sector     qualitat_aire  qualitat_o3     valor_o3      qualitat_no2 
 Ciutadella  : 738   --     : 162   --   : 287   Min.   :1.000   --     : 341  
 Eixample    : 738   Bona   :5571   Bona :4035   1st Qu.:2.000   Bona   :5358  
 Gràcia      : 738   Pobra  :   6   Pobra:   1   Median :4.000   Regular:  75  
 Observ Fabra: 738   Regular: 165   NA's :1581   Mean   :3.714   NA's   : 130  
 Palau Reial : 738                               3rd Qu.:5.000                 
 Poblenou    : 738                               Max.   :9.000                 
 (Other)     :1476                               NA's   :1868                  
   valor_no2     qualitat_pm10    valor_pm10       generat                   
 Min.   :1.000   --     :  42   Min.   :1.000   Min.   :2019-01-01 00:00:00  
 1st Qu.:2.000   Bona   :4238   1st Qu.:1.000   1st Qu.:2019-01-08 17:00:00  
 Median :3.000   Pobra  :   5   Median :1.000   Median :2019-01-16 14:30:00  
 Mean   :3.654   Regular: 102   Mean   :2.447   Mean   :2019-01-16 12:34:13  
 3rd Qu.:5.000   NA's   :1517   3rd Qu.:2.000   3rd Qu.:2019-01-24 07:00:00  
 Max.   :9.000                  Max.   :9.000   Max.   :2019-01-31 23:00:00  
 NA's   :471                    NA's   :1559                                 

gener2 %>% 
  filter(sector == "Palau Reial") %>% 
  select(valor_no2, generat) %>%
  ggplot(aes(x = generat, y = valor_no2)) +
  geom_line()

gener2 %>% 
  filter(sector == "Palau Reial", generat >= as.Date("2019-01-15"), generat <= as.Date("2019-01-16")) %>% 
  select(valor_no2, generat) %>%
  ggplot(aes(x = generat, y = valor_no2)) +
  geom_line() + geom_point()

NA

gener3 <- gener %>% 
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         date = str_extract(generat, ".*[[:space:]]"),
         hour = str_extract(generat, "(?<=[[:space:]])[0-9.]+")) %>%
  separate(col = date, into = c("day", "month", "year"), sep = "/") %>%
  mutate(day = as.integer(day),
         month = as.integer(month),
         year = as.integer(year),
         hour = as.integer(hour)) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, day, month, year, hour)
summary(gener3)

          sector     qualitat_aire  qualitat_o3     valor_o3      qualitat_no2 
 Ciutadella  : 738   --     : 162   --   : 287   Min.   :1.000   --     : 341  
 Eixample    : 738   Bona   :5571   Bona :4035   1st Qu.:2.000   Bona   :5358  
 Gràcia      : 738   Pobra  :   6   Pobra:   1   Median :4.000   Regular:  75  
 Observ Fabra: 738   Regular: 165   NA's :1581   Mean   :3.714   NA's   : 130  
 Palau Reial : 738                               3rd Qu.:5.000                 
 Poblenou    : 738                               Max.   :9.000                 
 (Other)     :1476                               NA's   :1868                  
   valor_no2     qualitat_pm10    valor_pm10         day            month  
 Min.   :1.000   --     :  42   Min.   :1.000   Min.   : 1.00   Min.   :1  
 1st Qu.:2.000   Bona   :4238   1st Qu.:1.000   1st Qu.: 8.00   1st Qu.:1  
 Median :3.000   Pobra  :   5   Median :1.000   Median :16.00   Median :1  
 Mean   :3.654   Regular: 102   Mean   :2.447   Mean   :16.05   Mean   :1  
 3rd Qu.:5.000   NA's   :1517   3rd Qu.:2.000   3rd Qu.:24.00   3rd Qu.:1  
 Max.   :9.000                  Max.   :9.000   Max.   :31.00   Max.   :1  
 NA's   :471                    NA's   :1559                               
      year           hour      
 Min.   :2019   Min.   : 0.00  
 1st Qu.:2019   1st Qu.: 5.00  
 Median :2019   Median :11.00  
 Mean   :2019   Mean   :11.43  
 3rd Qu.:2019   3rd Qu.:17.00  
 Max.   :2019   Max.   :23.00  
                               

BV: µg/m³ medio de contaminadores por horas en enero en 8 barrios de Barcelona.

gener3 %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year == 2019, month == 1) %>%
  select(day, hour, measure, measure_value, sector) %>%
  group_by(hour, measure, sector) %>%
  summarise(mean_val_hour = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(hour, mean_val_hour, color = measure)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Hora", 
       y = "µg/m³ medio",
       color = "Contaminador",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line() +
  geom_point() + 
  facet_wrap(~ sector, nrow = 2) +
  theme_minimal()

The study suggested that for 2015, the total existing UK vegetation reduces the average annual surface concentration by about 10% for PM2.5, 6% for PM10, 13% for O3, 24% for NH3 and 30% for SO2, but did not markedly change NO2 concentrations. https://airqualitynews.com/2018/07/30/plants-and-trees-not-the-solution-to-air-pollution-in-cities/

Cleaning dataset function:

clean_bcn_data <- function(data) {
  data <- data %>% 
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         date = str_extract(generat, ".*[[:space:]]"),
         hour = str_extract(generat, "(?<=[[:space:]])[0-9.]+")) %>%
  separate(col = date, into = c("day", "month", "year"), sep = "/") %>%
  mutate(day = as.integer(day),
         month = as.integer(month),
         year = as.integer(year),
         hour = as.integer(hour),
         month = case_when(month == 1 ~ as.integer(13), TRUE ~ as.integer(month))) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, day, month, year, hour)
  data
}

Aggregate data:

data_06_2018 <- tbl_df(fread("data/2018_06_Juny_qualitat_aire_BCN.csv", header=TRUE))
data_07_2018 <- tbl_df(fread("data/2018_07_Juliol_qualitat_aire_BCN.csv", header=TRUE))
data_08_2018 <- tbl_df(fread("data/2018_08_Agost_qualitat_aire_BCN.csv", header=TRUE))
data_09_2018 <- tbl_df(fread("data/2018_09_Setembre_qualitat_aire_BCN.csv", header=TRUE))
data_10_2018 <- tbl_df(fread("data/2018_10_Octubre_qualitat_aire_BCN.csv", header=TRUE))
data_12_2018 <- tbl_df(fread("data/2018_12_Desembre_qualitat_aire_BCN.csv", header=TRUE))
data_11_2018 <- tbl_df(fread("data/2018_11_novembre_qualitat_aire_BCN.csv", header=TRUE))
data_01_2019 <- tbl_df(fread("data/2019_01_Gener_qualitat_aire_BCN.csv", header=TRUE))
data <- rbind(data_06_2018,
              data_07_2018,
              data_08_2018,
              data_09_2018,
              data_10_2018,
              data_11_2018,
              data_12_2018,
              data_01_2019) %>% clean_bcn_data() 
summary(data)

         sector     qualitat_aire    qualitat_o3       valor_o3      qualitat_no2  
 Ciutadella :5320   --     : 2573   --     : 1893   Min.   :1.000   --     : 2302  
 Eixample   :5320   Bona   :36760   Bona   :27028   1st Qu.:2.000   Bona   :36990  
 Gràcia     :5320   Pobra  :  160   Pobra  :   18   Median :4.000   Regular:  595  
 Palau Reial:5320   Regular: 2394   Regular:  541   Mean   :4.196   NA's   : 2000  
 Poblenou   :5320                   NA's   :12407   3rd Qu.:6.000                  
 Sants      :5320                                   Max.   :9.000                  
 (Other)    :9967                                   NA's   :14309                  
   valor_no2     qualitat_pm10     valor_pm10         day            month       
 Min.   :1.000   --     :  542   Min.   :1.000   Min.   : 1.00   Min.   : 1.000  
 1st Qu.:2.000   Bona   :23621   1st Qu.:1.000   1st Qu.: 9.00   1st Qu.: 7.000  
 Median :3.000   Pobra  :  142   Median :2.000   Median :16.00   Median : 9.000  
 Mean   :3.548   Regular: 1393   Mean   :2.316   Mean   :16.34   Mean   : 8.155  
 3rd Qu.:5.000   NA's   :16189   3rd Qu.:3.000   3rd Qu.:24.00   3rd Qu.:11.000  
 Max.   :9.000                   Max.   :9.000   Max.   :31.00   Max.   :12.000  
 NA's   :4311                    NA's   :16740                                   
      year           hour     
 Min.   :2018   Min.   : 0.0  
 1st Qu.:2018   1st Qu.: 6.0  
 Median :2018   Median :12.0  
 Mean   :2018   Mean   :11.6  
 3rd Qu.:2018   3rd Qu.:18.0  
 Max.   :2019   Max.   :23.0  
                              

plotit <- data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
  group_by(month, hour, measure, sector) %>%
  summarise(mean_val_hour = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(hour, mean_val_hour, color = measure)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Hora", 
       y = "µg/m³ medio",
       color = "Contaminador",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line() +
  geom_point() + 
  facet_grid(sector ~ month) +
  theme_minimal()

data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
  group_by(month, measure, sector) %>%
  summarise(mean_val_month = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(month, mean_val_month, color = measure)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Mes", 
       y = "µg/m³ medio",
       color = "Contaminador",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line() +
  geom_point() + 
  facet_wrap(~ sector, nrow = 4) +
  theme_minimal()

plotit <- data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
  group_by(month, sector) %>%
  summarise(mean_val_month = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(month, mean_val_month)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Mes", 
       y = "µg/m³ medio",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  geom_line() +
  geom_point() + 
  facet_wrap(~ sector, nrow = 4) +
  theme_minimal()

library(plotly)
library(htmlwidgets)
saveWidget(ggplotly(plotit, dynamicTicks = FALSE), file = "meancont.html");

Just clean

clean_data <- function(data) {
  data <- data %>% 
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         date = str_extract(generat, ".*[[:space:]]"),
         hour = str_extract(generat, "(?<=[[:space:]])[0-9.]+")) %>%
  separate(col = date, into = c("day", "month", "year"), sep = "/") %>%
  mutate(day = as.integer(day),
         month = as.integer(month),
         year = as.integer(year),
         hour = as.integer(hour),
         month = as.integer(month)) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, day, month, year, hour)
  data
}

(p <- data_06_2018 %>% clean_data() %>%
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year == 2018, month == 6) %>%
  select(day, hour, measure, measure_value, sector) %>%
   mutate(measure = case_when(measure == "valor_no2" ~ "NO2",
                              measure == "valor_o3" ~ "O3",
                              measure == "valor_pm10" ~ "PM10",
                              TRUE ~ measure)) %>%
  group_by(hour, measure, sector) %>%
  summarise(mean_val_hour = mean(measure_value, na.rm = TRUE)) %>%
   
   
   
  ggplot(aes(hour, mean_val_hour, color = measure)) +
  labs(title = "Mean value of pollutants by hour in June 2018",
       x = "Hour", 
       y = "µg/m³",
       color = "Pollutant",
       caption = "Data from opendata-ajuntament.barcelona.cat") +
  #scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line() +
  geom_point() + 
  facet_wrap(~ sector, nrow = 2) +
  theme_mine()
  )

saveWidget(ggplotly(p, dynamicTicks = TRUE), file = "meanjun.html")

ggplotly(p) 

Mortalitat

mortalitat <- tbl_df(fread("2018_taxa_mortalitat.csv", header=TRUE))
mortalitat %>%
  select(Nom_districte, Nom_barri, Nombre) %>%
  group_by(Nom_districte) %>%
  summarise(mean_n = mean(Nombre)) %>%
  ggplot() +
  geom_bar(aes(x = Nom_districte, y = mean_n),stat = "identity")

theme_mine <- function () { 
    theme_bw(base_size=12, base_family="Montserrat") %+replace% 
        theme(
            panel.background  = element_blank(),
            plot.background = element_rect(fill="#ebfff5", colour=NA), 
            legend.background = element_rect(fill="transparent", colour=NA),
            legend.key = element_rect(fill="transparent", colour=NA)
        )
}

data_06_2019 <- tbl_df(fread("data/2019_06_juny_qualitat_aire_BCN.csv", header=TRUE))
summary(data_06_2019)

 CODI_PROVINCIA  PROVINCIA         CODI_MUNICIPI   MUNICIPI            ESTACIO     
 Min.   :8      Length:1164        Min.   :19    Length:1164        Min.   : 4.00  
 1st Qu.:8      Class :character   1st Qu.:19    Class :character   1st Qu.:43.00  
 Median :8      Mode  :character   Median :19    Mode  :character   Median :44.00  
 Mean   :8                         Mean   :19                       Mean   :44.52  
 3rd Qu.:8                         3rd Qu.:19                       3rd Qu.:54.00  
 Max.   :8                         Max.   :19                       Max.   :57.00  
                                                                                   
 CODI_CONTAMINANT      ANY            MES         DIA             H01        
 Min.   : 1.000   Min.   :2019   Min.   :6   Min.   : 1.00   Min.   :  0.20  
 1st Qu.: 7.000   1st Qu.:2019   1st Qu.:6   1st Qu.: 8.00   1st Qu.:  1.00  
 Median : 8.000   Median :2019   Median :6   Median :16.00   Median : 19.00  
 Mean   : 8.634   Mean   :2019   Mean   :6   Mean   :15.51   Mean   : 25.76  
 3rd Qu.:12.000   3rd Qu.:2019   3rd Qu.:6   3rd Qu.:23.00   3rd Qu.: 39.00  
 Max.   :14.000   Max.   :2019   Max.   :6   Max.   :30.00   Max.   :564.00  
                                                             NA's   :17      
     V01                 H02             V02                 H03        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  1.00   Class :character   1st Qu.:  1.00  
 Mode  :character   Median : 16.00   Mode  :character   Median : 15.00  
                    Mean   : 25.68                      Mean   : 24.83  
                    3rd Qu.: 39.00                      3rd Qu.: 39.00  
                    Max.   :509.00                      Max.   :325.00  
                    NA's   :17                          NA's   :17      
     V03                 H04             V04                 H05        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  1.00   Class :character   1st Qu.:  1.00  
 Mode  :character   Median : 15.00   Mode  :character   Median : 14.00  
                    Mean   : 23.87                      Mean   : 23.07  
                    3rd Qu.: 38.00                      3rd Qu.: 36.00  
                    Max.   :334.00                      Max.   :397.00  
                    NA's   :18                          NA's   :18      
     V05                 H06             V06                 H07        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  1.00   Class :character   1st Qu.:  2.00  
 Mode  :character   Median : 14.00   Mode  :character   Median : 18.00  
                    Mean   : 23.07                      Mean   : 27.44  
                    3rd Qu.: 35.00                      3rd Qu.: 41.00  
                    Max.   :301.00                      Max.   :425.00  
                    NA's   :18                          NA's   :18      
     V07                 H08             V08                 H09       
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.2  
 Class :character   1st Qu.:  3.00   Class :character   1st Qu.:  4.0  
 Mode  :character   Median : 24.00   Mode  :character   Median : 27.0  
                    Mean   : 35.24                      Mean   : 39.9  
                    3rd Qu.: 50.00                      3rd Qu.: 52.0  
                    Max.   :482.00                      Max.   :674.0  
                    NA's   :18                          NA's   :19     
     V09                 H10             V10                 H11        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  4.00   Class :character   1st Qu.:  4.00  
 Mode  :character   Median : 25.00   Mode  :character   Median : 23.00  
                    Mean   : 37.82                      Mean   : 34.67  
                    3rd Qu.: 51.25                      3rd Qu.: 50.00  
                    Max.   :759.00                      Max.   :492.00  
                    NA's   :28                          NA's   :40      
     V11                 H12             V12                 H13        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  3.00   Class :character   1st Qu.:  3.00  
 Mode  :character   Median : 21.00   Mode  :character   Median : 20.00  
                    Mean   : 31.71                      Mean   : 30.59  
                    3rd Qu.: 50.00                      3rd Qu.: 48.00  
                    Max.   :264.00                      Max.   :240.00  
                    NA's   :45                          NA's   :44      
     V13                 H14             V14                 H15        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  3.00   Class :character   1st Qu.:  2.00  
 Mode  :character   Median : 18.00   Mode  :character   Median : 15.00  
                    Mean   : 29.26                      Mean   : 26.85  
                    3rd Qu.: 45.00                      3rd Qu.: 39.00  
                    Max.   :160.00                      Max.   :166.00  
                    NA's   :80                          NA's   :70      
     V15                 H16             V16                 H17        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  2.00   Class :character   1st Qu.:  2.00  
 Mode  :character   Median : 14.00   Mode  :character   Median : 14.00  
                    Mean   : 26.52                      Mean   : 27.47  
                    3rd Qu.: 39.00                      3rd Qu.: 41.00  
                    Max.   :190.00                      Max.   :240.00  
                    NA's   :51                          NA's   :40      
     V17                 H18             V18                 H19        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  2.00   Class :character   1st Qu.:  2.00  
 Mode  :character   Median : 15.00   Mode  :character   Median : 16.00  
                    Mean   : 27.94                      Mean   : 28.18  
                    3rd Qu.: 42.00                      3rd Qu.: 44.00  
                    Max.   :318.00                      Max.   :239.00  
                    NA's   :40                          NA's   :45      
     V19                 H20             V20                 H21        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  2.00   Class :character   1st Qu.:  1.00  
 Mode  :character   Median : 17.00   Mode  :character   Median : 18.00  
                    Mean   : 28.52                      Mean   : 27.37  
                    3rd Qu.: 43.50                      3rd Qu.: 43.00  
                    Max.   :322.00                      Max.   :289.00  
                    NA's   :45                          NA's   :46      
     V21                 H22             V22                 H23        
 Length:1164        Min.   :  0.20   Length:1164        Min.   :  0.20  
 Class :character   1st Qu.:  1.00   Class :character   1st Qu.:  1.00  
 Mode  :character   Median : 19.00   Mode  :character   Median : 18.00  
                    Mean   : 26.62                      Mean   : 25.65  
                    3rd Qu.: 42.00                      3rd Qu.: 42.25  
                    Max.   :182.00                      Max.   :181.00  
                    NA's   :46                          NA's   :48      
     V23                 H24             V24           
 Length:1164        Min.   :  0.20   Length:1164       
 Class :character   1st Qu.:  1.00   Class :character  
 Mode  :character   Median : 18.00   Mode  :character  
                    Mean   : 25.92                     
                    3rd Qu.: 41.00                     
                    Max.   :338.00                     
                    NA's   :48                         

length(names(data_06_2019))

[1] 57

(i <- data_06_2019 %>%
  select(5:57) %>%
  mutate(measure = as.factor(case_when(CODI_CONTAMINANT == 1 ~ "SO2",
                              CODI_CONTAMINANT == 7 ~ "NO",
                              CODI_CONTAMINANT == 8 ~ "NO2",
                              CODI_CONTAMINANT == 12 ~ "NOx",
                              CODI_CONTAMINANT == 14 ~ "O3",
                              CODI_CONTAMINANT == 6 ~ "CO",
                              CODI_CONTAMINANT == 10 ~ "PM10",
                              TRUE ~ ""))) %>%
  mutate(sector = as.factor(case_when(ESTACIO == 4 ~ "Poblenou",
                              ESTACIO == 42 ~ "Sants",
                              ESTACIO == 43 ~ "Eixample",
                              ESTACIO == 44 ~ "Gràcia",
                              ESTACIO == 50 ~ "Ciutadella",
                              ESTACIO == 54 ~ "Vall Hebron",
                              ESTACIO == 57 ~ "Palau Reial",
                              TRUE ~ ""))) %>%
  gather(hour, measure_value, starts_with("H")) %>%
  gather(validation, val, starts_with("V")) %>% distinct() %>%
  mutate(day = as.integer(DIA),
         month = as.integer(MES),
         year = as.integer(ANY),
         hour = as.integer(str_extract(hour,"..$"))) %>%
  select(sector, year, month, day, hour, measure, measure_value) %>%
  #filter(sector == "Ciutadella") %>%
  group_by(hour, measure, sector) %>%
  summarise(mean_val_hour = mean(measure_value, na.rm = TRUE)) %>%
    
  ggplot(aes(hour, mean_val_hour, color = measure)) +
  labs(title = "Mean value of pollutants by hour in June 2019",
       x = "Hour", 
       y = "µg/m³",
       color = "Pollutant",
       caption = "Data from opendata-ajuntament.barcelona.cat") +
  #scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  scale_color_manual(values = c("purple", "yellow", "red", "orange", "green", "blue", "pink")) +
  geom_line(size=1) +
  geom_hline(yintercept = 90, linetype = 2, color = "red") +
  geom_hline(yintercept = 110, linetype = 2, color = "green") +
  #geom_hline(yintercept = 200, linetype = 2, color = "pink") +
  #geom_text(aes(0,90,label = "Limit NO2 by h", vjust = -1), color = "black") +
  #geom_point() + 
  scale_x_continuous(limits = c(0, 22)) + 
  facet_wrap(~ sector, nrow = 1) +
  theme_mine())

NA

ggplotly(i)